﻿using System.Data;
using Oracle.DataAccess.Client;
using System.Diagnostics;
using Oracle.DataAccess.Types;
using System;


namespace IP.Core.IPBusinessService
{
	/// <summary>
	/// Thực hiện các phép tính với CSDL. Chỉ có các Lập trình sư trực tiếp CORE làm mới phải quan tâm đến
	/// 
	/// 
	/// </summary>
	/// <remarks>
	/// Chỉ được gọi từ class us_object
	/// </remarks>
	public class BS_Object
	{
		#region Member
			private OracleConnection m_connection ;
			private bool m_have_connection = false;
			private OracleTransaction m_oracle_transaction;
			private bool m_have_transaction = false;
		#endregion
		
		#region Public Interface
		/// <summary>
		/// Đổ dữ liệu từ DB lên DataSet
		/// </summary>
		/// <param name="i_ds"> dataset</param>
		/// <param name="i_TableName"> bảng cần xử lý dữ liệu</param>
		/// <param name="i_strDieuKien"> điều kiện - có cả where ở đầu</param>
		
		public void FillDataset(DataSet i_ds, 
								string i_TableName, 
								string i_strDieuKien){
			//Lấy một dataset từ csdl dựa trên điều kiện cho trước
			Debug.Assert(m_have_connection,"Chưa có connection thì chưa làm được Filldata - CSUNG");
			CCommandBuilder v_cb = new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleDataAdapter v_da = CProvider.getAdapter();
			v_da.SelectCommand = v_cb.getSelectCommand(i_strDieuKien);
			//Lấy dữ liệu từ DB
			try{
				v_da.Fill(i_ds, i_TableName);
			}
			catch(System.Exception v_e){
				throw v_e;
			}
		}
		/// <summary>
		/// đổ dữ liệu ra dataset bằng command
		/// </summary>
		/// <param name="i_ds"></param>
		/// <param name="i_SelectCmd"></param>
		public void FillDatasetByCommandText(DataSet i_ds, OracleCommand i_SelectCmd){
			try {
				OracleDataAdapter v_da = new OracleDataAdapter(i_SelectCmd);
				v_da.Fill(i_ds.Tables[0]);				
			}
			catch(System.Exception v_e){
				throw v_e;
			}
        
		}
		/// <summary>
		/// thực hiện 1 command đã được set tham số
		/// </summary>
		/// <param name="i_cmd">command đã được client create</param>
		public void exec_command(OracleCommand  i_cmd){
			Debug.Assert( m_have_connection, "Sao khong start connection di da - CSUNG");
			i_cmd.Connection = m_connection;
			i_cmd.ExecuteNonQuery();
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="i_ds"></param>
		/// <param name="i_TableName"></param>
		public void Update(DataSet i_ds, string i_TableName){
			Debug.Assert(m_have_connection,"Phải có connection thì mới UPDATE được chứ - CSUNG");
			this.ExecuteUpdateCommand(i_ds,i_TableName);
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="i_ds"></param>
		/// <param name="i_TableName"></param>
		public void UpdateTextCommand(DataSet i_ds, string i_TableName){
			Debug.Assert(m_have_connection,"Phải có connection thì mới UPDATE được chứ - CSUNG");
			this.ExecuteUpdateCommandCS(i_ds,i_TableName);
		}

		public decimal Insert(DataSet i_ds, string i_TableName){
			Debug.Assert(m_have_connection,"Phải có connection thì mới insert được chứ - CSUNG");
			return this.ExecuteInsertCommand(i_ds,i_TableName);
		}


		/// <summary>
		/// Tạo ra kiểu gọi mới ( chưa biết tại sao kiểu cũ không họat động)
		/// 
		/// </summary>
		/// <param name="i_ds"></param>
		/// <param name="i_TableName"></param>
		/// <returns></returns>
		public decimal InsertTextCommand(DataSet i_ds, string i_TableName){
			Debug.Assert(m_have_connection,"Phải có connection thì mới insert được chứ - CSUNG");
			return this.ExecuteInsertCommandCS( i_ds, i_TableName);
		}
		
		public void DeleteByID(string i_TableName, decimal i_dcID){
			Debug.Assert(m_have_connection,"Phải có connection thì mới XÓA được chứ - CSUNG");
			this.ExecuteDeleteByIDCommand(i_dcID,i_TableName);
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="i_ds"></param>
		/// <param name="i_TableName"></param>
		/// <returns></returns>
		public bool isUpdatable(DataSet i_ds, string i_TableName){
			CCommandBuilder v_cb =new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleCommand v_cmd=  v_cb.getUpdatableCommand();
			try{
				v_cmd.ExecuteNonQuery();
				return true;
			}
			catch(Exception v_e){
				throw (v_e);
			}
		}
		/// <summary>
		/// 
		/// 
		/// </summary>
		public OracleConnection open_connection(){
			m_connection = CProvider.getConnection();
			m_have_connection = true;
			m_oracle_transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);
			m_have_transaction = true;
			return m_connection;
		}
		/// <summary>
		/// commit và close oracle connection
		/// </summary>
		public void commit_close_connection(){
			Debug.Assert(m_have_connection,"Chua co connection thi khong lam the nay duoc - CSUNG");
			//DBFacadeUtil.commit(m_connection);
			m_oracle_transaction.Commit();
			m_connection.Close();
			m_have_connection = false;
		}
		/// <summary>
		/// 
		/// </summary>
		public void commit_not_close_connection(){
			Debug.Assert(m_have_connection,"Chua co connection thi khong lam the nay duoc - CSUNG");
			Debug.Assert(m_have_transaction,"Khong phai la nguoi bat dau transaction thi khong ket thuc duoc - CSUNG");
			//DBFacadeUtil.commit(m_connection);
			m_oracle_transaction.Commit();
			m_oracle_transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// 
		/// </summary>
		public void rollback_close_connection(){
			Debug.Assert(m_have_connection,"Chua co connection thi khong lam the nay duoc - CSUNG");
			Debug.Assert(m_have_transaction,"Khong phai la nguoi bat dau transaction thi khong ket thuc duoc - CSUNG");
			//DBFacadeUtil.rollback(m_connection);
			m_oracle_transaction.Rollback();
			m_connection.Close();
			m_have_connection = false;
		}

		public bool have_connection(){
			return m_have_connection;
		}

		public void rollback_not_close_connection(){
			Debug.Assert(m_have_connection,"Chua co connection thi khong lam the nay duoc - CSUNG");
			Debug.Assert(m_have_transaction,"Khong phai la nguoi bat dau transaction thi khong ket thuc duoc - CSUNG");
			//DBFacadeUtil.rollback(m_connection);
			m_oracle_transaction.Rollback();
			m_oracle_transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// lấy connection
		/// </summary>
		/// <returns></returns>
		public OracleConnection get_connection(){
			//Sua lai
			Debug.Assert(m_have_connection, "BSObject nay chua co transaction");
			return m_connection;
		}
	/// <summary>
	/// tiếp tục một connection 
	/// </summary>
	/// <param name="i_connection"></param>
		public void use_connection(OracleConnection i_connection){
			m_connection=i_connection;
			m_have_connection=true;
		}
		
		public void set_savepoint(string i_str_savepoint_name){
			Debug.Assert(m_have_transaction,"Khong phai la nguoi bat dau transaction thi khong set duoc savepoint - CSUNG");
			m_oracle_transaction.Save(i_str_savepoint_name);
		}

		public void rollback_to_savepoint(string i_str_savepoint_name){
			Debug.Assert(m_have_transaction,"Khong phai la nguoi bat dau transaction thi khong duoc rollback- CSUNG");
			m_oracle_transaction.Rollback(i_str_savepoint_name);
		}

		#endregion
		
		#region Private Method

		private decimal ExecuteInsertCommandCS( DataSet i_ds, string i_TableName){
			CCommandBuilder v_cb =new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleCommand v_cmd = v_cb.get_client_side_InserCommand();
			v_cmd.ExecuteNonQuery();
			OracleDecimal v_oracle_decimal =(OracleDecimal) v_cmd.Parameters["op_id"].Value;
			return v_oracle_decimal.Value;
		}

		private void ExecuteUpdateCommandCS( DataSet i_ds, string i_TableName) {
			//client side command
			CCommandBuilder v_cb =new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleCommand v_cmd = v_cb.get_client_side_UpdateCommand();
			print_command(v_cmd);
			v_cmd.ExecuteNonQuery();
		}


		private void print_command(OracleCommand i_cmd){
				Debug.WriteLine(i_cmd.CommandText);
		
		}

		private decimal ExecuteInsertCommand( DataSet i_ds, string i_TableName){
			CCommandBuilder v_cb =new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleCommand v_cmd = v_cb.getInsertCommand();	
			foreach (OracleParameter v_para in v_cmd.Parameters){
				Debug.WriteLine( v_para.ParameterName + " " + v_para.OracleDbType.ToString() + "  " + v_para.Direction.ToString() + "  " + v_para.Value.ToString());
			}
			v_cmd.ExecuteNonQuery();
			OracleDecimal v_oracle_decimal =(OracleDecimal) v_cmd.Parameters["op_id"].Value;
			return v_oracle_decimal.Value;
 		}
		
		private void ExecuteUpdateCommand( DataSet i_ds, string i_TableName)
		{
			CCommandBuilder v_cb =new CCommandBuilder(m_connection,i_ds,i_TableName);
			OracleCommand v_cmd = v_cb.getUpdateCommand();
			v_cmd.ExecuteNonQuery();
		}

		private void ExecuteDeleteByIDCommand(decimal i_dcID,  string i_TableName)
		{
			DBStoredProc v_stored_proc = new DBStoredProc( CCommandBuilder.getDeleteByIDCommandText(i_TableName));
			v_stored_proc.addDecimalInputParam("ip_id",i_dcID);
			v_stored_proc.exec_command(m_connection);
		}			
	
		
		#endregion
	}
}
